﻿CREATE PROCEDURE [dbo].[proc_Orders_Department_Count]
	(
		@StartDate nvarchar(50),
		@EndDate nvarchar (50),
		@DepartmentId int,
		@MainProjectId int,
		@Stext nvarchar(50)
	)
AS
--	;WITH ChildDepartment(ID,DName,PId) AS
--	(
--		SELECT ID,DName,ParentID FROM department WHERE ParentID=@departmentid or id=@departmentid
--	    
--		UNION ALL
--		SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
--		INNER JOIN department ON d.ID = department.ParentID
--	)

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

	select count(id) from orders 
	where (select e.departmentid from employee e where e.username=Orders.UserName) in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)
         and (Convert(varchar(10),FixtureDate,120)>=@StartDate and Convert(varchar(10),FixtureDate,120)<=@EndDate)
         and (@MainProjectId=0 or MainProjectId=@MainProjectId)
         and ((select e.ename from employee e where e.username=Orders.username) like  '%'+@SText+'%' or isnull((Select CustomerName From Customer Where CustomerId=Orders.CustomerId),'...') like '%'+@SText+'%')  
	RETURN
